package com.carb.service.impl;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.carb.constant.ReportConstant;
import com.carb.constant.MessageConstant;
import com.carb.dto.EmployeeServiceDTO;
import com.carb.dto.MemberConsumptionDTO;
import com.carb.dto.ProjectSalesDTO;
import com.carb.dto.ProjectStatisticsDTO;
import com.carb.entity.Business;
import com.carb.entity.Project;
import com.carb.enums.BusinessStatus;
import com.carb.enums.UserRole;
import com.carb.exception.EntityNotFoundException;
import com.carb.exception.ExportFailedException;
import com.carb.mapper.*;
import com.carb.service.ReportService;
import com.carb.vo.*;
import jakarta.annotation.Resource;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * <p>
 * 统计报表 服务实现类
 * </p>
 *
 * @author XuChaoyang
 * @since 2025-02-11
 */
@Service
@Slf4j
public class ReportServiceImpl implements ReportService {

    @Resource
    private BusinessMapper businessMapper;

    @Resource
    private UserMapper userMapper;

    @Resource
    private ProjectMapper projectMapper;

    @Resource
    private EmployeeMapper employeeMapper;

    @Resource
    private MemberMapper memberMapper;

    @Resource
    private CarMapper carMapper;

    /**
     * 营业额统计
     * @param begin 开始日期
     * @param end 结束日期
     * @return 营业额统计VO
     */
    @Override
    public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
        // 1、构建日期列表
        List<LocalDate> dateList = getDateList(begin, end);

        // 2、获取营业额列表
        List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Double turnover = businessMapper.getSumTurnover(beginTime, endTime);
            turnover = turnover == null ? 0.0 : turnover / 100;
            turnoverList.add(turnover);
        }

        // 3、封装VO
        return TurnoverReportVO.builder()
                .dateList(dateList)
                .turnoverList(turnoverList)
                .build();
    }

    /**
     * 获取日期列表
     * @param begin 开始日期
     * @param end 结束日期
     * @return 日期列表
     */
    private static List<LocalDate> getDateList(LocalDate begin, LocalDate end) {
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);
        while (!begin.equals(end)) {
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        return dateList;
    }

    /**
     * 会员数量统计
     * @param begin 开始日期
     * @param end 结束日期
     * @return 用户统计VO
     */
    @Override
    public MemberReportVO getMemberStatistics(LocalDate begin, LocalDate end) {
        // 1、构建日期列表
        List<LocalDate> dateList = getDateList(begin, end);

        // 2、获取会员总数列表、新增会员列表
        List<Integer> totalMemberList = new ArrayList<>();
        List<Integer> newMemberList = new ArrayList<>();
        for (LocalDate date : dateList) {
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            // 会员总数 select count(id) from tb_user where create_time < ?
            Integer totalMember = userMapper.getUserCount(null, endTime, UserRole.MEMBER);
            // 新增会员 select count(id) from tb_user where create_time > ? and create_time < ?
            Integer newMember = userMapper.getUserCount(beginTime, endTime, UserRole.MEMBER);
            totalMemberList.add(totalMember);
            newMemberList.add(newMember);
        }

        // 3、封装VO
        return MemberReportVO.builder()
                .dateList(dateList)
                .totalMemberList(totalMemberList)
                .newMemberList(newMemberList)
                .build();
    }

    /**
     * 业务统计
     * @param begin 开始日期
     * @param end 结束日期
     * @return 业务统计VO
     */
    @Override
    public BusinessReportVO getBusinessStatistics(LocalDate begin, LocalDate end) {
        // 1、构建日期列表
        List<LocalDate> dateList = getDateList(begin, end);

        // 2、获取每日业务数列表、每日有效业务数列表
        List<Integer> businessCountList = new ArrayList<>();
        List<Integer> validBusinessCountList = new ArrayList<>();
        for (LocalDate date : dateList) {
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Integer businessCount = businessMapper.getBusinessCount(beginTime, endTime, null);
            Integer validBusinessCount = businessMapper.getBusinessCount(beginTime, endTime, BusinessStatus.COMPLETED);
            businessCountList.add(businessCount);
            validBusinessCountList.add(validBusinessCount);
        }

        // 3、获取业务总数、有效业务总数、业务完成率
        Integer totalBusinessCount = businessCountList.stream().reduce(Integer::sum).orElse(0);
        Integer totalValidBusinessCount = validBusinessCountList.stream().reduce(Integer::sum).orElse(0);
        Double businessCompletionRate = totalBusinessCount == 0 ? 0.0 : totalValidBusinessCount.doubleValue() / totalBusinessCount;

        // 4、封装VO
        return BusinessReportVO.builder()
                .dateList(dateList)
                .businessCountList(businessCountList)
                .validBusinessCountList(validBusinessCountList)
                .totalBusinessCount(totalBusinessCount)
                .totalValidBusinessCount(totalValidBusinessCount)
                .businessCompletionRate(businessCompletionRate)
                .build();
    }

    /**
     * 服务项目排名统计
     * @param begin 开始日期
     * @param end 结束日期
     * @return 服务项目排名
     */
    @Override
    public List<ProjectSalesDTO> getProjectTop10(LocalDate begin, LocalDate end) {
        // 1、构建开始时间和结束时间
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

        // 2、获取服务项目排名列表
        return projectMapper.getProjectTop10(beginTime, endTime);
    }

    /**
     * 员工服务排名统计
     * @param begin 开始日期
     * @param end 结束日期
     * @return 员工服务排名
     */
    @Override
    public List<EmployeeServiceDTO> getEmployeeTop10(LocalDate begin, LocalDate end) {
        // 1、构建开始时间和结束时间
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

        // 2、获取员工服务排名列表
        return employeeMapper.getEmployeeTop10(beginTime, endTime);
    }

    /**
     * 会员消费排名统计
     * @param begin 开始日期
     * @param end 结束日期
     * @return 会员消费排名
     */
    @Override
    public List<MemberConsumptionDTO> getMemberTop10(LocalDate begin, LocalDate end) {
        // 1、构建开始时间和结束时间
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

        // 2、获取会员消费排名列表
       return memberMapper.getMemberTop10(beginTime, endTime);
    }

    /**
     * 导出近30天的业务统计报表
     * @param response 响应
     */
    @Override
    public void exportBusinessStatisticsReport(HttpServletResponse response) {
        try {
            // 获取近30天的开始/结束日期、开始/结束时间
            LocalDate begin = LocalDate.now().minusDays(30);
            LocalDate end = LocalDate.now().minusDays(1);
            LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

            // 如果没有数据，则抛出异常
            Integer totalBusinessCount = businessMapper.getBusinessCount(beginTime, endTime, null);
            if (totalBusinessCount == 0) {
                throw new ExportFailedException(MessageConstant.BUSINESS_STATISTICS_REPORT_NO_DATA);
            }

            // 获取模板文件输入流
            InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(ReportConstant.BUSINESS_REPORT_TEMPLATE_PATH);
            if (inputStream == null) {
                throw new ExportFailedException(MessageConstant.BUSINESS_REPORT_TEMPLATE_NOT_FOUND);
            }

            // 1、业务统计报表
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);
            XSSFSheet businessSheet = excel.getSheet(ReportConstant.BUSINESS_STATISTICS_SHEET);
            System.out.println(businessSheet.getLastRowNum());
            // 日期
            businessSheet.getRow(2).getCell(0).setCellValue(begin.toString());
            businessSheet.getRow(5).getCell(0).setCellValue(end.toString());
            // 营业额（总计）
            Double turnover = businessMapper.getSumTurnover(beginTime, endTime);
            businessSheet.getRow(2).getCell(2).setCellValue(turnover == null ? 0.0 : turnover / 100);
            // 业务总数（总计）
            businessSheet.getRow(3).getCell(2).setCellValue(totalBusinessCount);
            // 有效业务数（总计）
            Integer validBusinessCount = businessMapper.getBusinessCount(beginTime, endTime, BusinessStatus.COMPLETED);
            businessSheet.getRow(4).getCell(2).setCellValue(validBusinessCount);
            // 业务完成率（总计）
            businessSheet.getRow(5).getCell(2).setCellValue(validBusinessCount.doubleValue() / totalBusinessCount);
            // 30天明细
            for (int i = 0; i < 30; i++) {
                LocalDate date = begin.plusDays(i);
                LocalDateTime dateBeginTime = LocalDateTime.of(date, LocalTime.MIN);
                LocalDateTime dateEndTime = LocalDateTime.of(date, LocalTime.MAX);
                XSSFRow row = businessSheet.getRow(8 + i);
                // 日期
                row.getCell(0).setCellValue(date.toString());
                // 业务总数
                totalBusinessCount = businessMapper.getBusinessCount(dateBeginTime, dateEndTime, null);
                if (totalBusinessCount == 0) continue; // 如果没有数据则跳过，减少数据库查询次数
                row.getCell(2).setCellValue(totalBusinessCount);
                // 营业额
                turnover = businessMapper.getSumTurnover(dateBeginTime, dateEndTime);
                row.getCell(1).setCellValue(turnover == null ? 0.0 : turnover / 100);
                // 有效业务数
                validBusinessCount = businessMapper.getBusinessCount(dateBeginTime, dateEndTime, BusinessStatus.COMPLETED);
                row.getCell(3).setCellValue(validBusinessCount);
                // 业务完成率
                row.getCell(4).setCellValue(validBusinessCount.doubleValue() / totalBusinessCount);
            }

            // 2、服务项目Top10
            XSSFSheet projectSheet = excel.getSheet(ReportConstant.PROJECT_TOP10_SHEET);
            projectSheet.getRow(1).getCell(0).setCellValue(begin + " 至 " + end);
            List<ProjectSalesDTO> projectTop10 = projectMapper.getProjectTop10(beginTime, endTime);
            for (int i = 0; i < projectTop10.size(); i++) {
                ProjectSalesDTO projectSales = projectTop10.get(i);
                XSSFRow row = projectSheet.getRow(3 + i);
                // 排名
                row.getCell(0).setCellValue(i + 1);
                // 服务项目id
                row.getCell(1).setCellValue(projectSales.getProjectId());
                // 服务项目名称
                row.getCell(2).setCellValue(projectSales.getProjectName());
                // 销量
                row.getCell(3).setCellValue(projectSales.getSalesCount());
                // 价格
                row.getCell(4).setCellValue(projectSales.getProjectPrice() / 100);
                // 评分
                Double projectRating = projectSales.getProjectRating();
                if (projectRating == null || projectRating == 0) {
                    row.getCell(5).setCellValue("暂无评分");
                } else {
                    row.getCell(5).setCellValue(projectRating);
                }
            }

            // 3、员工服务Top10
            XSSFSheet employeeSheet = excel.getSheet(ReportConstant.EMPLOYEE_TOP10_SHEET);
            employeeSheet.getRow(1).getCell(0).setCellValue(begin + " 至 " + end);
            List<EmployeeServiceDTO> employeeTop10 = employeeMapper.getEmployeeTop10(beginTime, endTime);
            for (int i = 0; i < employeeTop10.size(); i++) {
                EmployeeServiceDTO employeeService = employeeTop10.get(i);
                XSSFRow row = employeeSheet.getRow(3 + i);
                // 排名
                row.getCell(0).setCellValue(i + 1);
                // 员工id
                row.getCell(1).setCellValue(employeeService.getEmployeeId());
                // 员工姓名
                row.getCell(2).setCellValue(employeeService.getEmployeeName());
                // 服务次数
                row.getCell(3).setCellValue(employeeService.getServiceCount());
                // 服务金额
                row.getCell(4).setCellValue(employeeService.getServiceAmount() / 100);
            }

            // 4、会员消费Top10
            XSSFSheet memberSheet = excel.getSheet(ReportConstant.MEMBER_TOP10_SHEET);
            memberSheet.getRow(1).getCell(0).setCellValue(begin + " 至 " + end);
            List<MemberConsumptionDTO> memberTop10 = memberMapper.getMemberTop10(beginTime, endTime);
            for (int i = 0; i < memberTop10.size(); i++) {
                MemberConsumptionDTO memberConsumption = memberTop10.get(i);
                XSSFRow row = memberSheet.getRow(3 + i);
                // 排名
                row.getCell(0).setCellValue(i + 1);
                // 会员id
                row.getCell(1).setCellValue(memberConsumption.getMemberId());
                // 会员姓名
                row.getCell(2).setCellValue(memberConsumption.getMemberName());
                // 消费次数
                row.getCell(3).setCellValue(memberConsumption.getConsumeCount());
                // 消费金额
                row.getCell(4).setCellValue(memberConsumption.getConsumeAmount() / 100);
            }

            // 指定响应类型和文件名（UTF-8编码），通过输出流将文件下载到浏览器中
            String fileName = ReportConstant.BUSINESS_EXPORT_FILE_NAME
                    + "(" + begin.format(DateTimeFormatter.ofPattern("yyyyMMdd")) + "~"
                    + end.format(DateTimeFormatter.ofPattern("yyyyMMdd")) + ").xlsx";
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replace("+", "%20");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName + "\"; filename*=UTF-8''" + encodedFileName);
            ServletOutputStream outputStream = response.getOutputStream();
            excel.write(outputStream);

            // 关闭资源
            outputStream.flush();
            outputStream.close();
            excel.close();
            inputStream.close();
        } catch (IOException e) {
            log.error(e.getMessage());
            throw new ExportFailedException(MessageConstant.BUSINESS_REPORT_EXPORT_FAILED);
        }
    }

    /**
     * 导出指定服务项目报表
     * @param response 响应
     * @param id 服务项目id
     */
    @Override
    public void exportProjectStatisticsReport(HttpServletResponse response, Long id) {
        // 1、检查服务项目是否存在
        Project project = projectMapper.selectById(id);
        if (project == null) {
            // 服务项目不存在
            throw new EntityNotFoundException(MessageConstant.PROJECT_NOT_FOUND);
        }

        // 2、根据服务项目id获取业务信息
        List<Business> businessList = businessMapper.selectList(Wrappers.lambdaQuery(Business.class)
                .eq(Business::getProjectId, id));
        // 如果没有数据，则抛出异常
        if (businessList.isEmpty()) {
            throw new EntityNotFoundException(MessageConstant.PROJECT_STATISTICS_REPORT_NO_DATA);
        }

        // 3、使用in查询一次性获取所有关联数据
        // 3.1、获取所有关联数据的id集合
        List<Long> projectIds = new ArrayList<>();
        List<Long> employeeIds = new ArrayList<>();
        List<Long> memberIds = new ArrayList<>();
        List<Long> carIds = new ArrayList<>();
        List<Long> businessIds = new ArrayList<>();
        for (Business business : businessList) {
            projectIds.add(business.getProjectId());
            employeeIds.add(business.getEmployeeId());
            memberIds.add(business.getMemberId());
            carIds.add(business.getCarId());
            businessIds.add(business.getId());
        }
        // 3.2、获取所有关联数据的id-name集合
        Map<Long, String> projectNameMap = projectMapper.getProjectNamesByIds(projectIds).stream()
                .collect(Collectors.toMap(map -> Long.valueOf(map.get("projectId").toString()), map -> map.get("projectName").toString()));
        Map<Long, String> employeeNameMap = employeeMapper.getEmployeeNamesByIds(employeeIds).stream()
                .collect(Collectors.toMap(map -> Long.valueOf(map.get("employeeId").toString()), map -> map.get("employeeName").toString()));
        Map<Long, String> memberNameMap = memberMapper.getMemberNamesByIds(memberIds).stream()
                .collect(Collectors.toMap(map -> Long.valueOf(map.get("memberId").toString()), map -> map.get("memberName").toString()));
        Map<Long, String> carPlateNumberMap = carMapper.getCarPlateNumbersByIds(carIds).stream()
                .collect(Collectors.toMap(map -> Long.valueOf(map.get("carId").toString()), map -> map.get("plateNumber").toString()));
        Map<Long, Double> businessRatingMap = businessMapper.getRatingsByIds(businessIds).stream()
                .collect(Collectors.toMap(map -> Long.valueOf(map.get("businessId").toString()), map -> Double.valueOf(map.get("rating").toString())));

        try {
            // 获取模板文件输入流
            InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(ReportConstant.PROJECT_REPORT_TEMPLATE_PATH);
            if (inputStream == null) {
                throw new ExportFailedException(MessageConstant.PROJECT_REPORT_TEMPLATE_NOT_FOUND);
            }
            
            // 创建Excel对象和sheet
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);
            XSSFSheet projectSheet = excel.getSheet(ReportConstant.PROJECT_STATISTICS_SHEET);
            System.out.println(projectSheet.getLastRowNum());
            // 总计
            ProjectStatisticsDTO projectStatistics = projectMapper.getProjectStatistics(id);
            XSSFRow row = projectSheet.getRow(2);
            // 销售总额
            Double projectPrice = projectStatistics.getProjectPrice();
            row.getCell(1).setCellValue(projectPrice == null ? 0.0 : projectPrice / 100);
            // 销售数量
            row.getCell(4).setCellValue(projectStatistics.getSalesCount());
            // 平均服务时长
            Double avgServiceTime = projectStatistics.getAvgServiceTime();
            avgServiceTime = avgServiceTime == null ? 0 : avgServiceTime;
            row.getCell(7).setCellValue(avgServiceTime + "分钟");
            // 平均评分
            Double avgProjectRating = projectStatistics.getAvgProjectRating();
            row.getCell(10).setCellValue(avgProjectRating == null ? 0.0 : avgProjectRating);

            // 明细
            int rowNum = 5;
            for (Business business : businessList) {
                row = projectSheet.createRow(rowNum++);
                // 业务id
                row.createCell(0).setCellValue(business.getId());
                // 服务项目名称
                row.createCell(1).setCellValue(projectNameMap.get(business.getProjectId()));
                // 员工姓名
                String employeeName = employeeNameMap.get(business.getEmployeeId());
                row.createCell(2).setCellValue(Objects.requireNonNullElse(employeeName, "未接单"));
                // 会员姓名
                row.createCell(3).setCellValue(memberNameMap.get(business.getMemberId()));
                // 车牌号
                row.createCell(4).setCellValue(carPlateNumberMap.get(business.getCarId()));
                // 开始时间
                row.createCell(5).setCellValue(business.getCreateTime());
                // 结束时间
                row.createCell(6).setCellValue(business.getUpdateTime());
                // 服务时长
                Integer actualDuration = business.getActualDuration();
                actualDuration = actualDuration == null ? 0 : actualDuration;
                row.createCell(7).setCellValue(actualDuration + "分钟");
                // 付款方式
                row.createCell(8).setCellValue(business.getPayMethod().getDesc());
                // 付款金额
                row.createCell(9).setCellValue(business.getPayableAmount().doubleValue() / 100);
                // 业务状态
                row.createCell(10).setCellValue(business.getStatus().getDesc());
                // 会员评分
                Double rating = businessRatingMap.get(business.getId());
                if (rating == null || rating == 0) {
                    row.createCell(11).setCellValue("暂无评分");
                } else {
                    row.createCell(11).setCellValue(rating);
                }
            }

            // 指定响应类型和文件名（UTF-8编码），通过输出流将文件下载到浏览器中
            String fileName = ReportConstant.PROJECT_EXPORT_FILE_NAME
                    + "(" + project.getName() + ").xlsx";
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replace("+", "%20");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName + "\"; filename*=UTF-8''" + encodedFileName);
            ServletOutputStream outputStream = response.getOutputStream();
            excel.write(outputStream);

            // 关闭资源
            outputStream.flush();
            outputStream.close();
            excel.close();
            inputStream.close();
        } catch (IOException e) {
            log.error(e.getMessage());
            throw new ExportFailedException(MessageConstant.PROJECT_REPORT_EXPORT_FAILED);
        }
    }

}
